import 'dart:io';
import 'dart:convert';
import 'dart:typed_data';
import 'package:flutter/foundation.dart';
import 'package:spreadsheet_decoder/spreadsheet_decoder.dart';
import 'package:archive/archive.dart';
import '../models/question_model.dart';

/// 增强的Excel导入服务
/// 使用第三方库支持更多Excel格式
class EnhancedExcelImportService {
  
  /// 导入Excel文件并解析为题目列表
  static Future<List<Question>> importFromExcel(File file) async {
    try {
      final fileName = file.path.split('/').last;
      debugPrint('=== 开始使用增强Excel服务导入文件 ===');
      debugPrint('文件名: $fileName');
      debugPrint('文件路径: ${file.path}');

      // 检查文件是否存在
      if (!await file.exists()) {
        debugPrint('❌ 文件不存在');
        return [];
      }

      // 读取文件内容
      final bytes = await file.readAsBytes();
      debugPrint('文件大小: ${bytes.length} 字节');

      // 检测并解析Excel文件
      final spreadsheet = await _parseExcelFile(bytes, fileName);
      if (spreadsheet == null) {
        debugPrint('❌ Excel文件解析失败');
        return [];
      }

      debugPrint('✅ Excel文件解析成功');
      debugPrint('工作表数量: ${spreadsheet.tables.length}');

      // 解析题目数据
      final questions = await _extractQuestionsFromSpreadsheet(spreadsheet, fileName);
      
      debugPrint('=== Excel导入完成 ===');
      debugPrint('成功导入题目数量: ${questions.length}');
      
      return questions;

    } catch (e, stackTrace) {
      debugPrint('❌ Excel导入过程中发生错误: $e');
      debugPrint('错误堆栈: $stackTrace');
      return [];
    }
  }

  /// 解析Excel文件
  static Future<SpreadsheetDecoder?> _parseExcelFile(Uint8List bytes, String fileName) async {
    try {
      // 检测文件格式
      final format = _detectFileFormat(bytes, fileName);
      debugPrint('检测到文件格式: $format');

      SpreadsheetDecoder? spreadsheet;

      // 根据格式选择解析方式
      switch (format) {
        case ExcelFormat.xlsx:
          spreadsheet = await _parseXLSX(bytes);
          break;
        case ExcelFormat.xls:
          spreadsheet = await _parseXLS(bytes);
          break;
        case ExcelFormat.ods:
          spreadsheet = await _parseODS(bytes);
          break;
        case ExcelFormat.unknown:
          // 尝试所有格式
          spreadsheet = await _parseWithFallback(bytes);
          break;
      }

      return spreadsheet;
    } catch (e) {
      debugPrint('解析Excel文件时出错: $e');
      return null;
    }
  }

  /// 检测文件格式
  static ExcelFormat _detectFileFormat(Uint8List bytes, String fileName) {
    if (bytes.length < 8) return ExcelFormat.unknown;

    // 检查文件扩展名
    final extension = fileName.toLowerCase().split('.').last;
    
    // 检查文件头
    if (bytes[0] == 0x50 && bytes[1] == 0x4B) {
      // ZIP格式 - 可能是XLSX或ODS
      if (extension == 'xlsx') return ExcelFormat.xlsx;
      if (extension == 'ods') return ExcelFormat.ods;
      return ExcelFormat.xlsx; // 默认尝试XLSX
    }
    
    if (bytes[0] == 0xD0 && bytes[1] == 0xCF && bytes[2] == 0x11 && bytes[3] == 0xE0) {
      // OLE2格式 - 传统XLS
      return ExcelFormat.xls;
    }

    // 根据扩展名推测
    switch (extension) {
      case 'xlsx':
        return ExcelFormat.xlsx;
      case 'xls':
        return ExcelFormat.xls;
      case 'ods':
        return ExcelFormat.ods;
      default:
        return ExcelFormat.unknown;
    }
  }

  /// 解析XLSX格式
  static Future<SpreadsheetDecoder?> _parseXLSX(Uint8List bytes) async {
    try {
      debugPrint('尝试解析XLSX格式...');
      final decoder = SpreadsheetDecoder.decodeBytes(bytes);
      debugPrint('✅ XLSX解析成功');
      return decoder;
    } catch (e) {
      debugPrint('❌ XLSX解析失败: $e');
      return null;
    }
  }

  /// 解析XLS格式
  static Future<SpreadsheetDecoder?> _parseXLS(Uint8List bytes) async {
    try {
      debugPrint('尝试解析XLS格式...');
      final decoder = SpreadsheetDecoder.decodeBytes(bytes);
      debugPrint('✅ XLS解析成功');
      return decoder;
    } catch (e) {
      debugPrint('❌ XLS解析失败: $e');
      return null;
    }
  }

  /// 解析ODS格式
  static Future<SpreadsheetDecoder?> _parseODS(Uint8List bytes) async {
    try {
      debugPrint('尝试解析ODS格式...');
      final decoder = SpreadsheetDecoder.decodeBytes(bytes);
      debugPrint('✅ ODS解析成功');
      return decoder;
    } catch (e) {
      debugPrint('❌ ODS解析失败: $e');
      return null;
    }
  }

  /// 使用回退策略解析
  static Future<SpreadsheetDecoder?> _parseWithFallback(Uint8List bytes) async {
    debugPrint('使用回退策略，尝试所有格式...');
    
    // 尝试XLSX
    var result = await _parseXLSX(bytes);
    if (result != null) return result;
    
    // 尝试XLS
    result = await _parseXLS(bytes);
    if (result != null) return result;
    
    // 尝试ODS
    result = await _parseODS(bytes);
    if (result != null) return result;
    
    debugPrint('❌ 所有格式解析都失败了');
    return null;
  }

  /// 从电子表格中提取题目
  static Future<List<Question>> _extractQuestionsFromSpreadsheet(
    SpreadsheetDecoder spreadsheet, 
    String fileName
  ) async {
    try {
      // 获取第一个工作表
      final tableName = spreadsheet.tables.keys.first;
      final table = spreadsheet.tables[tableName]!;
      
      debugPrint('工作表名称: $tableName');
      debugPrint('行数: ${table.maxRows}');
      debugPrint('列数: ${table.maxCols}');

      if (table.maxRows < 2) {
        debugPrint('❌ 工作表数据不足');
        return [];
      }

      // 解析表头
      final headers = _parseHeaders(table);
      if (headers.isEmpty) {
        debugPrint('❌ 无法解析表头');
        return [];
      }

      debugPrint('解析到的表头: $headers');

      // 解析题目数据
      final questions = <Question>[];
      for (int rowIndex = 1; rowIndex < table.maxRows; rowIndex++) {
        try {
          final question = _parseQuestionRow(table, rowIndex, headers);
          if (question != null) {
            questions.add(question);
            debugPrint('成功解析第$rowIndex行题目:');
            debugPrint('  ID: ${question.id}');
            debugPrint('  标题: ${question.text}');
            debugPrint('  类型: ${question.type}');
            debugPrint('  分数: ${question.score}');
            if (question is SingleChoiceQuestion) {
              debugPrint('  选项数量: ${question.choices.length}');
              debugPrint('  正确答案: ${question.correctOption}');
            } else if (question is MultipleChoiceQuestion) {
              debugPrint('  选项数量: ${question.choices.length}');
              debugPrint('  正确答案: ${question.correctOptions}');
            }
          }
        } catch (e) {
          debugPrint('解析第$rowIndex行时出错: $e');
        }
      }

      return questions;
    } catch (e) {
      debugPrint('提取题目数据时出错: $e');
      return [];
    }
  }

  /// 解析表头
  static Map<String, int> _parseHeaders(SpreadsheetTable table) {
    final headers = <String, int>{};

    if (table.maxRows == 0) return headers;

    for (int col = 0; col < table.maxCols; col++) {
      final cellValue = table.rows[0][col];
      if (cellValue != null) {
        String headerText = cellValue.toString();

        // 尝试Base64解码
        try {
          if (headerText.isNotEmpty) {
            final decoded = utf8.decode(base64.decode(headerText));
            headerText = decoded;
          }
        } catch (e) {
          // 解码失败，使用原始值
        }

        if (headerText.isNotEmpty) {
          headers[headerText] = col;
        }
      }
    }

    return headers;
  }

  /// 解析题目行
  static Question? _parseQuestionRow(
    SpreadsheetTable table, 
    int rowIndex, 
    Map<String, int> headers
  ) {
    try {
      // 获取必需字段
      final idCol = headers['题目id'];
      final titleCol = headers['题目名称'];
      final typeCol = headers['题目类型'];
      final scoreCol = headers['题目分数'];
      
      if (idCol == null || titleCol == null || typeCol == null || scoreCol == null) {
        debugPrint('缺少必需的列');
        return null;
      }

      // 读取基本信息
      final rawId = _getCellValue(table, rowIndex, idCol);
      final rawTitle = _getCellValue(table, rowIndex, titleCol);
      final typeStr = _getCellValue(table, rowIndex, typeCol);
      final scoreStr = _getCellValue(table, rowIndex, scoreCol);

      // 解码Base64字段
      final id = _decodeBase64String(rawId);
      final title = _decodeBase64String(rawTitle);

      debugPrint('原始数据: ID=$rawId, Title=$rawTitle');
      debugPrint('解码后: ID=$id, Title=$title');

      if (id.isEmpty || title.isEmpty || typeStr.isEmpty) {
        return null;
      }

      // 解析题目类型和分数
      final type = int.tryParse(typeStr) ?? 1;
      final score = _parseScore(scoreStr);

      // 根据题目类型解析具体内容
      return _createQuestionByType(table, rowIndex, headers, id, title, type, score);
      
    } catch (e) {
      debugPrint('解析题目行时出错: $e');
      return null;
    }
  }

  /// 获取单元格值
  static String _getCellValue(SpreadsheetTable table, int row, int col) {
    try {
      if (row >= table.maxRows || col >= table.maxCols) {
        return '';
      }
      final cell = table.rows[row][col];
      return cell?.toString() ?? '';
    } catch (e) {
      return '';
    }
  }

  /// 解析分数
  static int _parseScore(String scoreStr) {
    try {
      // 尝试Base64解码
      final decoded = utf8.decode(base64.decode(scoreStr));
      return int.tryParse(decoded) ?? 2;
    } catch (e) {
      return int.tryParse(scoreStr) ?? 2;
    }
  }

  /// 根据类型创建题目
  static Question? _createQuestionByType(
    SpreadsheetTable table,
    int rowIndex,
    Map<String, int> headers,
    String id,
    String title,
    int type,
    int score,
  ) {
    try {
      // 获取选项和答案列
      final optionsCol = headers['题目选项'];
      final answerCol = headers['题目答案'];
      final explanationCol = headers['答案分析'];

      String optionsStr = '';
      String answerStr = '';
      String explanationStr = '';

      if (optionsCol != null) {
        optionsStr = _getCellValue(table, rowIndex, optionsCol);
        optionsStr = _decodeBase64String(optionsStr);
      }

      if (answerCol != null) {
        answerStr = _getCellValue(table, rowIndex, answerCol);
        answerStr = _decodeBase64String(answerStr);
      }

      if (explanationCol != null) {
        explanationStr = _getCellValue(table, rowIndex, explanationCol);
        explanationStr = _decodeBase64String(explanationStr);
      }

      // 根据题目类型处理
      switch (type) {
        case 1: // 单选题
          return _createSingleChoiceQuestion(id, title, score, optionsStr, answerStr, explanationStr);
        case 2: // 多选题
          return _createMultipleChoiceQuestion(id, title, score, optionsStr, answerStr, explanationStr);
        case 3: // 判断题
          return _createTrueFalseQuestion(id, title, score, answerStr, explanationStr);
        case 4: // 填空题
          return _createFillBlankQuestion(id, title, score, answerStr, explanationStr);
        case 5: // 问答题
          return _createEssayQuestion(id, title, score, answerStr, explanationStr);
        default:
          debugPrint('未知题目类型: $type');
          return null;
      }
    } catch (e) {
      debugPrint('创建题目时出错: $e');
      return null;
    }
  }

  /// 解码Base64字符串
  static String _decodeBase64String(String input) {
    if (input.isEmpty) return input;

    try {
      return utf8.decode(base64.decode(input));
    } catch (e) {
      return input; // 解码失败，返回原始字符串
    }
  }

  /// 创建单选题
  static Question _createSingleChoiceQuestion(
    String id, String title, int score, String optionsStr, String answerStr, String explanationStr
  ) {
    final choiceTexts = _parseChoices(optionsStr);
    final correctIndex = _findCorrectChoiceIndex(choiceTexts, answerStr);

    // 创建Choice对象列表
    final choices = choiceTexts.asMap().entries.map((entry) {
      return Choice(
        id: String.fromCharCode(65 + entry.key), // A, B, C, D...
        text: entry.value,
      );
    }).toList();

    return SingleChoiceQuestion(
      id: id,
      text: title,
      score: score,
      choices: choices,
      correctOption: correctIndex >= 0 ? correctIndex : 0,
      analysis: explanationStr.isNotEmpty ? explanationStr : null,
    );
  }

  /// 创建多选题
  static Question _createMultipleChoiceQuestion(
    String id, String title, int score, String optionsStr, String answerStr, String explanationStr
  ) {
    final choiceTexts = _parseChoices(optionsStr);
    final correctAnswers = _parseMultipleAnswers(answerStr);

    // 创建Choice对象列表
    final choices = choiceTexts.asMap().entries.map((entry) {
      return Choice(
        id: String.fromCharCode(65 + entry.key), // A, B, C, D...
        text: entry.value,
      );
    }).toList();

    // 将字母答案转换为索引
    final correctOptions = correctAnswers.map((answer) {
      final index = answer.codeUnitAt(0) - 65; // A=0, B=1, C=2...
      return index >= 0 && index < choices.length ? index : 0;
    }).toList();

    return MultipleChoiceQuestion(
      id: id,
      text: title,
      score: score,
      choices: choices,
      correctOptions: correctOptions,
      analysis: explanationStr.isNotEmpty ? explanationStr : null,
    );
  }

  /// 创建判断题
  static Question _createTrueFalseQuestion(
    String id, String title, int score, String answerStr, String explanationStr
  ) {
    final isTrue = answerStr.contains('正确') || answerStr.contains('对') || answerStr.contains('T') || answerStr.contains('true');

    return TrueOrFalseQuestion(
      id: id,
      text: title,
      score: score,
      correctAnswer: isTrue,
      analysis: explanationStr.isNotEmpty ? explanationStr : null,
    );
  }

  /// 创建填空题
  static Question _createFillBlankQuestion(
    String id, String title, int score, String answerStr, String explanationStr
  ) {
    // 处理多空填空题，使用逗号分隔多个答案
    final answers = _parseFillBlankAnswers(answerStr);
    final correctAnswer = answers.join(','); // FillBlankQuestion内部会解析逗号分隔的答案

    return FillBlankQuestion(
      id: id,
      text: title,
      score: score,
      correctAnswer: correctAnswer,
      analysis: explanationStr.isNotEmpty ? explanationStr : null,
    );
  }

  /// 创建问答题
  static Question _createEssayQuestion(
    String id, String title, int score, String answerStr, String explanationStr
  ) {
    return ShortAnswerQuestion(
      id: id,
      text: title,
      score: score,
      referencedAnswer: answerStr,
      analysis: explanationStr.isNotEmpty ? explanationStr : null,
    );
  }

  /// 解析选择题选项
  static List<String> _parseChoices(String optionsStr) {
    debugPrint('🔍 开始解析选项: "$optionsStr"');

    if (optionsStr.isEmpty) {
      debugPrint('❌ 选项字符串为空');
      return [];
    }

    // 首先尝试解析JSON格式
    if (optionsStr.startsWith('[') && optionsStr.endsWith(']')) {
      debugPrint('🔍 检测到JSON格式选项，尝试解析');
      try {
        final List<dynamic> jsonList = json.decode(optionsStr);
        final choices = jsonList.map((item) {
          if (item is Map<String, dynamic>) {
            return item['desc']?.toString() ?? '';
          }
          return item.toString();
        }).where((choice) => choice.isNotEmpty).toList();

        debugPrint('✅ JSON格式解析成功，解析到 ${choices.length} 个选项: $choices');
        return choices;
      } catch (e) {
        debugPrint('❌ JSON格式解析失败: $e，尝试其他格式');
      }
    }

    // 尝试多种分隔符
    List<String> choices = [];

    if (optionsStr.contains('♀')) {
      debugPrint('✅ 使用♀分隔符解析选项');
      choices = optionsStr.split('♀');
    } else if (optionsStr.contains('|')) {
      debugPrint('✅ 使用|分隔符解析选项');
      choices = optionsStr.split('|');
    } else if (optionsStr.contains('\n')) {
      debugPrint('✅ 使用换行符解析选项');
      choices = optionsStr.split('\n');
    } else if (optionsStr.contains('；')) {
      debugPrint('✅ 使用中文分号解析选项');
      choices = optionsStr.split('；');
    } else if (optionsStr.contains(';')) {
      debugPrint('✅ 使用英文分号解析选项');
      choices = optionsStr.split(';');
    } else {
      debugPrint('🔍 尝试按A. B. C. D.格式解析选项');
      // 尝试按A. B. C. D.格式解析
      final regex = RegExp(r'[A-Z]\.\s*([^A-Z]+)(?=[A-Z]\.|$)');
      final matches = regex.allMatches(optionsStr);
      choices = matches.map((match) => match.group(1)?.trim() ?? '').toList();
      debugPrint('📝 正则表达式匹配到 ${choices.length} 个选项');
    }

    final finalChoices = choices.where((choice) => choice.trim().isNotEmpty).map((choice) => choice.trim()).toList();
    debugPrint('✅ 最终解析到 ${finalChoices.length} 个选项: $finalChoices');

    return finalChoices;
  }

  /// 查找正确选项索引
  static int _findCorrectChoiceIndex(List<String> choices, String answerStr) {
    if (choices.isEmpty || answerStr.isEmpty) return 0;

    // 尝试按字母匹配 (A, B, C, D)
    if (answerStr.length == 1 && answerStr.codeUnitAt(0) >= 65 && answerStr.codeUnitAt(0) <= 90) {
      return answerStr.codeUnitAt(0) - 65;
    }

    // 尝试按内容匹配
    for (int i = 0; i < choices.length; i++) {
      if (choices[i].contains(answerStr) || answerStr.contains(choices[i])) {
        return i;
      }
    }

    return 0; // 默认返回第一个选项
  }

  /// 解析多选题答案
  static List<String> _parseMultipleAnswers(String answerStr) {
    if (answerStr.isEmpty) return ['A'];

    final answers = <String>[];

    // 按逗号或其他分隔符分割
    final parts = answerStr.split(RegExp(r'[,，、]'));

    for (final part in parts) {
      final trimmed = part.trim();
      if (trimmed.isNotEmpty) {
        // 如果是单个字母，直接添加
        if (trimmed.length == 1 && trimmed.codeUnitAt(0) >= 65 && trimmed.codeUnitAt(0) <= 90) {
          answers.add(trimmed);
        } else {
          // 尝试提取字母
          final match = RegExp(r'[A-Z]').firstMatch(trimmed);
          if (match != null) {
            answers.add(match.group(0)!);
          }
        }
      }
    }

    return answers.isEmpty ? ['A'] : answers;
  }

  /// 解析填空题答案
  static List<String> _parseFillBlankAnswers(String answerStr) {
    if (answerStr.isEmpty) return [''];

    // 尝试多种分隔符
    List<String> answers = [];

    if (answerStr.contains('，')) {
      answers = answerStr.split('，');
    } else if (answerStr.contains(',')) {
      answers = answerStr.split(',');
    } else if (answerStr.contains('|')) {
      answers = answerStr.split('|');
    } else if (answerStr.contains(';')) {
      answers = answerStr.split(';');
    } else if (answerStr.contains('；')) {
      answers = answerStr.split('；');
    } else {
      answers = [answerStr];
    }

    return answers.map((answer) => answer.trim()).where((answer) => answer.isNotEmpty).toList();
  }
}

/// Excel文件格式枚举
enum ExcelFormat {
  xlsx,
  xls,
  ods,
  unknown,
}
